Understanding Functional Dependency

In explaining functional dependency to students, I have noticed in texts a mixture of two types of elements: intensional (or psychological or meaning) and extensional (patterns of repetition in the data). In this chapter I examine whether it is possible to consider functional dependency, in particular, in second and third normal forms, solely on an extensional basis. The Microsoft Access Analyzer utility seems to do so. I illustrate the mix of intensional and extensional elements in textbook definitions of functional dependency. I conclude that although in principle first, second and third normal form can be done solely by extensional means, in practice intensional considerations are indispensable. Finally, I discuss these questions with respect to the “higher order” normal forms, namely Boyce-Codd, fourth, fifth and Domain/Key normal form. INTRODUCTION In attempting to explain the process of normalization of databases to students, I have noticed that texts differ in small but significant ways in how they explain the concept of functional dependency, which is necessary to define and to implement the normal forms. 701 E. Chocolate Avenue, Hershey PA 17033-1117, USA Tel: 717/533-8845; Fax 717/533-8661; URL-http://www.irm-press.com IRM PRESS This chapter appears in the book, Effective Databases for Text & Document Management by Shirley A. Becker. Copyright © 2003, IRM Press, an imprint of Idea Group Inc. Copying or distributing in print or electronic forms without written permission of Idea Group Inc. is prohibited. Understanding Functional Dependency 279 Copyright © 2003, Idea Group Inc. Copying or distributing in print or electronic forms without written permission of Idea Group Inc. is prohibited. I think this disparity is due to a problem in the concept of functional dependency itself. Although intuitively clear, it is actually a mixture of two quite different elements: • Psychological or meaning elements involving dependencies in knowledge — for example, we need to know customer name in order to know customer address. (I will call these intensional elements.) • Objective elements derived solely from the data about the objects represented in the database in some way reflected in tables. (I will call these extensional elements. The idea is that an extensional element must be based on differences in the way data appear in the table — most notably, patterns of repetition of field values.) The main motivation for normalization and the normal forms is the elimination of “bad” data redundancies in the database. A tool such as Analyzer in Microsoft Access is able to accomplish this without using any intensional elements or indeed without even considering the meaning of the field names. In logical terminology, its procedures are purely extensional. All that Analyzer uses are patterns of repetition of field values. We can use repetitions in the data to determine first, second and third normal forms once primary keys are determined. Of course this depends on the data not being misleading. I will discuss whether the following extensional conjecture is true. The Extensional Conjecture: All possible combinations of the data allow normalization (first, second and third normal form) on the basis of repetitions in the data alone (supposing primary keys are given). Seemingly, identifying the primary key from existing fields involves intensional elements. Just looking at a table filled with data doesn’t decide whether the customer social security number or customer credit card number is to be the primary key. Nevertheless, it is an extensional matter whether or not a field or fields can be a primary key — in other words, whether the field or fields is a candidate key. If there are (genuine) duplications in the values for the field, we do not have a candidate key. Analyzer usually does not identify primary keys for tables from field values. Instead, for each table it often assigns a new field with non-repeating incremental numerical values, or it allows the user to pre-identify one or more fields in a table as the primary key. The situation here will turn out to mirror the situation with first, second and third normal forms: all can be defined extensionally, but in practice, intensional elements may be all but indispensable because we are normally not in possession of all possible combinations of field values. Near the end of the chapter, I will comment on the applicability of my conclusions to the “higher” normal forms, namely Boyce-Codd, fourth, fifth and domain-key normal forms. My procedure will first be to recap material from standard logical works on the distinction between intension and extension. Then I will examine several sample definitions of functional dependency from popular texts on database design to show how intensional and extensional elements are mixed. Following that, I will examine the issue of whether the Extensional Conjecture is true. I will conclude by discussing the implications both for teaching and for using the concept of functional dependency and the normal forms. 8 more pages are available in the full version of this document, which may be purchased using the "Add to Cart" button on the publisher's webpage: www.igi-global.com/chapter/understanding-functionaldependency/9216